'''
Created on 2011-4-28

@author: Administrator
'''
import web;
import datetime;
import predef;
import net.sevear.douban.DBUserAPI;

db = web.database(dbn='mysql', user='tomorrp1_dbchaos', pw='huyue860529', db='tomorrp1_dbchaos', host=predef.DB_HOST,port=3306,charset='utf8',use_unicode=0);
#db = web.database(dbn='mysql', user='tomorrp1_dbchaos', pw='huyue860529', db='tomorrp1_dbchaos', host='host172.hostmonster.com',port=3306,charset='utf8');


def getDBUser(uid):
    rst =  db.select('user',where='uid=%d'%uid);
    if(len(rst)==0):
        api = net.sevear.douban.DBUserAPI.DBUserAPI()
        return api.searchDBUser(uid);
    else:
        return rst[0];

def getDBUserByAlias(alias):
    rst = db.select('user',where="dbuid='%s'"%alias);
    if(len(rst)==0):
        return None;
    else:
        return rst[0];
    
def getDBUserByNames(name):
    return db.select('user',where="name='%s' and isactivate=1"%name);
    
def addDBUser(uid,name,url,uidstr,headimg,version=""):
    db.insert('user',uid=uid,
              name=name,dburl=url,dbuid=uidstr,headimg=headimg,enable=1,version=version);
    
def addUnActivateUser(uid,name,alias,url,headimg):
    db.insert('user',uid=uid,name=name,dburl=url,dbuid=alias,headimg=headimg,isactivate=0);
    
def updateDBUserTime(uid):
    db.update('user',where='uid=%d'%uid,
              activietime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"));
              
def activateDBUser(uid,version,contacs):
    db.update('user',where='uid=%d'%uid,
              isactivate = 1,
              version = version,
              tolist = contacs,
              sendlist = contacs,
              enable = 1,
              activietime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"));
              
def updateDBUserInfo(uid,uidstr,name,url,headimg,version):
    db.update('user',where='uid=%d'%uid,
              activietime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
              dbuid=uidstr,
              name=name,
              dburl=url,
              headimg=headimg,
              version=version);
    
def getDBUserContacts(uid):
    rst = db.select('user',where='uid=%d'%uid);
    if(rst!=None and len(rst)>0):
        dbUSer = rst[0]
        list = [];
            
        if(dbUSer.tolist!=None and dbUSer.tolist!=""):
            tolist = str(dbUSer.tolist).split(',');
            for toId in tolist:
                if(toId==None or toId==''):
                    print '%s tolist contains invalid value '%uid;
                    return [];
                toUserRst = getDBUser(int(toId));
                if(toUserRst!=None):
                    toUser = toUserRst;
                    if(toUser.isactivate==0):
                        list.append({"uid":toUser.uid,
                                 "activietime":""
                                 ,"uidstr":toUser.dbuid
                                 ,"dburl":toUser.dburl
                                 ,"headimg":toUser.headimg
                                 ,"isactivate":toUser.isactivate
                                 ,"name":toUser.name});
                    else:
                        list.append({"uid":toUser.uid,
                                 "activietime":toUser.activietime.strftime("%Y-%m-%d %H:%M:%S")
                                 ,"uidstr":toUser.dbuid
                                 ,"dburl":toUser.dburl
                                 ,"headimg":toUser.headimg
                                 ,"isactivate":toUser.isactivate
                                 ,"name":toUser.name});
#        if(dbUSer.sendlist!=None and dbUSer.sendlist!=""):
#            sendlist = str(dbUSer.sendlist).split(',');
#            for sendid in sendlist:
#                if((sendid in tolist)==False):
#                    sendUserRst = getDBUser(int(sendid));
#                    if(sendUserRst!=None):
#                        sendUser = sendUserRst;
#                        list.append({"uid":sendUser.uid,
#                                     "activietime":sendUser.activietime.strftime("%Y-%m-%d %H:%M:%S")
#                                     ,"uidstr":sendUser.dbuid
#                                     ,"dburl":sendUser.dburl
#                                     ,"headimg":toUser.headimg
#                                     ,"name":sendUser.name});
        return list;
    else:
        return [];

def getContactListStr(uid):
    rst = db.select('user',where='uid=%d'%uid);
    if(len(rst)>0):
        return rst[0].tolist;
    else:
        return "";

def updateContacts(uid,newLists):
    db.update('user',where='uid=%d'%uid,tolist=newLists,sendlist=newLists);

def getInviteItem(id):
    rst = db.select('invites',where='id=%d'%id);
    if(len(rst)>0):
        return rst[0];
    else:
        return None;
    
def getInvitationsByInvietedID(dID):
    return db.select('invites',where="dbid=%d"%dID);
    
def getInviteItemByUid(toid):
    return db.select('invites',where='dbid=%d'%toid);
    
def deleteInviteItem(id):
    db.delete('invites',where='id=%d'%id);

def addInviteItem(uid,inviteduid,code,invitedName="",invitedAlias="",songid=-1):
    return db.insert('invites',inviterid=uid,dbid=inviteduid,dbalias=invitedAlias,dbname=invitedName,resongid=songid,
                     time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),hashcode=code);

def getTrack(sid):
    rst = db.select('track',where='sid=%d'%sid);
    if(len(rst)>0):
        return rst[0];
    else:
        return None;

def addTrack(track):
    return db.insert('track',sid=track['sid'],name=track['name'],ssid=track['ssid'],
                     aid=track['aid'],aname=track['albumName'],
                     acoverurl=track['albumImgURL'],adburl=track['albumDBURL'],artist=track['artistName'],public_time=track['public_time'],\
                     company=track['company'], length=track['length'], rating_avg=track['rating_avg'],listenurl=track['listenURL']);
            
def updateTrackSSID(sid,ssid):
    db.update('track',where='sid=%d'%sid,ssid=ssid);
    
def addRecommendation(sourceId,destId,sid,time=None):
    if(time==None):
        return db.insert('recommend',sourceid=sourceId,destid=destId,songid=sid,time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),feedback=0);
    else:
        return db.insert('recommend',sourceid=sourceId,destid=destId,songid=sid,time=time,feedback=0);

def addRecomendations(sourceId,destIds,sid):
    list = [];
    for destid in destIds:
        list.append({"sourceid":sourceId,"destid":destid,"songid":sid,
                     "time":datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                     "feedback":0});
    db.multiple_insert('recommend',list,seqname="songid");
    rids = db.query('SELECT last_insert_id() FROM recommend;');
    lastIds=[];
    if(len(rids)>0):
        startIdx =  int(rids[0]["last_insert_id()"]);
        for destid in destIds:
            lastIds.append(startIdx);
            startIdx = startIdx+1;
            pass
    return lastIds;

    
def getRecommendation(rId):
    rst = db.select('recommend',where='id=%d'%rId);
    if(len(rst)>0):
        return rst[0]
    else:
        return None;


def getUnResponedReInDestId(destID,starttime):
    rst = db.select('recommend',where='destid=%d AND time>"%s" and (feedback=1 OR feedback=0)'%(destID,starttime),limit=10);
    return rst;

def getRecentLikeRecommend(sourceID,destID):
    q = "SELECT * FROM  `recommend` WHERE sourceid =%d AND destid =%d AND feedback =2 ORDER BY TIME DESC"%(sourceID,destID)
    rst = db.query(q)
    if(len(rst)>0):
        return rst[0]
    else:
        return None;

def getResponedInSourceId(sourceId,rids):
    rst = db.select('recommend',where='sourceid=%d'%sourceId);
    list = {};
    for item in rst:
        if(str(item.id) in rids):
            list[item.id] = item.feedback;
    return list;

def setReItemFlag(rid,flag):
    db.update('recommend',where='id=%d AND feedback!=2'%rid,feedback=flag);

def setReIteSent(destId):
    db.update('recommend',where='destid=%d'%destId,feedback=1);


def getUnConsumedEvent(uid):
    rst = db.select('events',where='uid=%d'%uid);
    return rst;

def getTypedEvent(uid,type):
    t = db.transaction()
    try:
        rst = db.select('events',where='uid=%d AND type=%d'%(uid,type));
        db.delete('events',where='uid=%d AND type=%d'%(uid,type));
    except:
        t.rollback()
        raise
    else:
        t.commit()
   
    return rst;

def addEvent(type,uid,content):
    return db.insert('events',
                     uid=uid,
                     content=content,
                     type=type,
                     time=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"));
                     
def getRecommendTotal(sourceID,destID):
    q = "SELECT COUNT(*) FROM  `recommend` WHERE sourceid =%d AND destid =%d ORDER BY TIME DESC"%(sourceID,destID)
    return db.query(q)[0]

def getRecommendLikeCount(sourceID,destID):
    q = "SELECT COUNT(*) FROM  `recommend` WHERE sourceid =%d AND destid =%d AND feedback =2 ORDER BY TIME DESC"%(sourceID,destID)
    return db.query(q)[0]

def updateP2pSiteOpen(userid,opentime):
    db.update('p2psites',where='userid=%d'%userid,lastopentime=
              opentime.strftime("%Y-%m-%d %H:%M:%S"));
    return db.select('p2psites',where='userid=%d'%userid)[0];

def updateP2pSiteClose(userid,len):
    db.update('p2psites',where='userid=%d'%userid,lasttimelength=len);
    
def updateSiteListeners(siteownerid,newlistenrs):
    db.update('p2psites',where='userid=%d'%siteownerid,listeners = ','.join(newlistenrs))

def getP2pSite(siteOwnerid):
    rst = db.select('p2psites',where='userid=%d'%siteOwnerid)
    if(len(rst)>0):
        return rst[0]
    else:
        return None;
    
def createP2pSite(siteOwnerid,name):
    db.insert('p2psites',userid=siteOwnerid,name=name,lastopentime=
              datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))